package sql

import org.apache.commons.beanutils.BeanUtils
import org.apache.commons.beanutils.BeanUtilsBean
import org.apache.commons.beanutils.converters.DateConverter
import org.apache.commons.dbutils.QueryRunner
import org.apache.commons.dbutils.handlers.BeanHandler
import org.apache.commons.dbutils.handlers.BeanListHandler
import org.apache.commons.dbutils.handlers.ScalarHandler
import java.io.Serializable
import java.lang.reflect.Field
import java.sql.SQLException
import java.util.*


abstract class InterfaceDao<T>(val TABLE_NAME: String) : Serializable {

    init {
        val convertUtils = BeanUtilsBean.getInstance().convertUtils
        val dateConverter = DateConverter()
        dateConverter.setPattern("yyyy-MM-dd HH:mm:ss")
        convertUtils.register(dateConverter, String::class.java)
    }

    fun Field.isBasicType():Boolean{
        when (type) {
            String::class.java -> {
                return true
            }
            Int::class.java -> {
                return true
            }
            Boolean::class.java -> {
                return true
            }
            Float::class.java -> {
                return true
            }
            Date::class.java -> {
                return true
            }
            else -> {
                return false
            }
        }
    }

    /**
     *
     */
    fun createTable(t: Class<out T>): Boolean {
        val head =
            "CREATE TABLE IF NOT EXISTS $TABLE_NAME (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, "
        var middle = ""
        val tail = ")"
        val fields = t.declaredFields
        fields.forEach {
            if (it.name != "id") {
                val typeStr = when (it.type) {
                    String::class.java -> {
                        "VARCHAR(255)"
                    }
                    Int::class.java -> {
                        "INT"
                    }
                    Boolean::class.java -> {
                        "INT"
                    }
                    Float::class.java -> {
                        "FLOAT"
                    }
                    Date::class.java -> {
                        "DATETIME"
                    }
                    else -> {
                        "ELSE"
                    }
                }
                if (typeStr != "ELSE") {
                    middle += "${it.name} ${typeStr}, "
                }
            }
        }
        middle = middle.substring(0, middle.lastIndexOf(","))
        val sql = "$head $middle $tail"
        val runner = QueryRunner(DBSUtl.getDataSource())
        return try {
            val result = runner.execute(sql)
            result == 0
        } catch (e: SQLException) {
            e.printStackTrace()
            false
        }
    }

    /**
     * 保存一个T对象
     */
    fun save(stu: T) {
        val map = BeanUtils.describe(stu)
        val head = "insert ignore into $TABLE_NAME ("
        var middle1 = ""
        var middle2 = ""
        val tail1 = ") values("
        val tail2 = ");"

        val fields = stu!!::class.java.declaredFields
        fields.forEach {
            if(it.isBasicType()){
                val key = it.name
                if (key != "id" && key != "class") {
                    middle1 += "$key, "
                    middle2 += "\"${map[key]}\", "
                }
            }
        }

        middle1 = middle1.substring(0, middle1.lastIndexOf(", "))
        middle2 = middle2.substring(0, middle2.lastIndexOf(", "))
        val sql = "$head$middle1$tail1$middle2$tail2"
        val qr = QueryRunner(DBSUtl.getDataSource())
        try {
            qr.update(sql)
        } catch (e: SQLException) {
            e.printStackTrace()
        }
    }

    /**
     * 更新一个T对象信息
     */
    fun update(id: Int, stu: T) {
        val map = BeanUtils.describe(stu)
        val head = "update $TABLE_NAME set "
        var middle = ""
        val tail = " where id=$id"

        val fields = stu!!::class.java.declaredFields
        fields.forEach {
            if(it.isBasicType()){
                val key = it.name
                val value = map[key]
                if (key != "id" && key != "class") {
                    middle += if (value == "true" || value == "false") {
                        "$key=\"${if (value == "true") 1 else 0}\", "
                    } else {
                        "$key=\"$value\", "
                    }
                }
            }
        }

        middle = middle.substring(0, middle.lastIndexOf(", "))

        val sql = "$head$middle$tail"

        val qr = QueryRunner(DBSUtl.getDataSource())
        try {
            qr.update(sql)
        } catch (e: SQLException) {
            e.printStackTrace()
        }
    }

    /**
     * 删除T对象
     */
    fun delete(id: Int) {
        val sql = "delete from $TABLE_NAME where id = ?"
        val qr = QueryRunner(DBSUtl.getDataSource())
        try {
            qr.update(sql, id)
        } catch (e: SQLException) {
            e.printStackTrace()
        }
    }

    /**
     * 获取指定T对象
     */
    fun get(t: Class<out T>, id: Int): T? {
        val sql = "select * from $TABLE_NAME where id = ?"
        val qr = QueryRunner(DBSUtl.getDataSource())
        return try {
            qr.query(sql, BeanHandler(t), id)
        } catch (e: SQLException) {
            e.printStackTrace()
            null
        }
    }

    /**
     * 获取所有的T对象
     */
    fun getAll(t: Class<out T>): List<T>? {
        val sql = "select * from $TABLE_NAME"
        val qr = QueryRunner(DBSUtl.getDataSource())
        //获取全部记录
        return try {
            qr.query(sql, BeanListHandler(t))
        } catch (e: SQLException) {
            e.printStackTrace()
            null
        }
    }

    /**
     * 获取T对象的总数
     */
    fun getCount(): Int {
        val sql = "select count(*) as count from $TABLE_NAME"
        val qr = QueryRunner(DBSUtl.getDataSource())

        return try {
            //必须long强转
            val toInt = (qr.query<Any?>(sql, ScalarHandler()) as Long).toInt()
            toInt
        } catch (e: SQLException) {
            e.printStackTrace()
            0
        }
    }

    /**
     * 防止单例对象在反序列化时重新生成对象
     * 由于反序列化时会调用readResolve这个钩子方法，只需要把当前的KSingleton对象返回而不是去创建一个新的对象
     */
    abstract fun readResolve(): Any
}